﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;

namespace GSManager
{
    public class KhachHang
    {
        private string _MaKH;
        private string _TenKH;
        private string _DiaChi;
        private string _SoDT;
        private decimal _TienNo;
        public string MaKH
        {
            get{return _MaKH;}
            set{_MaKH=value;}
        }
        public  string TenKH
        {
            get{return _TenKH;}
            set{_TenKH=value;}
        }
        public string DiaChi
        {
            get{return _DiaChi;}
            set{_DiaChi=value;}
        }
        public string SoDT
        {
            get{return _SoDT;}
            set{_SoDT=value;}
        }
        public decimal TienNo
        {
            get{return _TienNo;}
            set{_TienNo=value;}
        }
        public DataSet LayDanhSach()
        {
            OleDbConnection connect = KetNoi.KetNoiDuLieu();
            string sql = "select * from KHACHHANG";
            OleDbDataAdapter adapter = new OleDbDataAdapter(sql, connect);
            DataSet ds = new DataSet();
            adapter.Fill(ds, "KHACHHANG");
            connect.Close();
            return ds;
        }
        public void ThemKhachHang()
        {
            OleDbConnection connect = KetNoi.KetNoiDuLieu();
            string sql = "Insert into KHACHHANG values(?,?,?,?,?)";
            OleDbCommand cmd = new OleDbCommand(sql, connect);
            cmd.Parameters.Add("@MaKhachHang", OleDbType.VarChar);
            cmd.Parameters.Add("@TenKhachHang", OleDbType.VarWChar);
            cmd.Parameters.Add("@DiaChi", OleDbType.VarWChar);
            cmd.Parameters.Add("@SoDT", OleDbType.VarChar);
            cmd.Parameters.Add("@TienNo", OleDbType.Decimal);
            cmd.Parameters[0].Value = MaKH;
            cmd.Parameters[1].Value = TenKH;
            cmd.Parameters[2].Value = DiaChi;
            cmd.Parameters[3].Value = SoDT;
            cmd.Parameters[4].Value = TienNo;
            cmd.ExecuteNonQuery();
            connect.Close();
        }
        public void XoaKhachHang(string makh)
        {
            OleDbConnection connect = KetNoi.KetNoiDuLieu();
            string sql = "Delete from KHACHHANG where MaKhachHang='" + makh + "'";
            OleDbCommand cmd = new OleDbCommand(sql, connect);
            cmd.ExecuteNonQuery();
            connect.Close();
        }
        public void CapNhatThongTin()
        {
            OleDbConnection connect = KetNoi.KetNoiDuLieu();
            string sql = "Update KHACHHANG set DiaChi=?, SoDT=? where MaKhachHang=?";
            OleDbCommand cmd = new OleDbCommand(sql, connect);
            cmd.Parameters.Add("@DiaChi", OleDbType.VarWChar);
            cmd.Parameters.Add("@SoDT", OleDbType.VarChar);
            cmd.Parameters.Add("@MaKhachHang", OleDbType.VarChar);
            cmd.Parameters[0].Value = DiaChi;
            cmd.Parameters[1].Value = SoDT;
            cmd.Parameters[2].Value = MaKH;
            cmd.ExecuteNonQuery();
            connect.Close();
        }
        public void CapNhatTienNo()
        {
            OleDbConnection connect = KetNoi.KetNoiDuLieu();
            string sql = "Update KHACHHANG set TienNo="+TienNo+"where MaKhachHang='"+MaKH+"'";
            OleDbCommand cmd = new OleDbCommand(sql, connect);
            
            cmd.ExecuteNonQuery();
            connect.Close();
        }
        public AutoCompleteStringCollection LayDanhSachTenKhachHang()
        {
            OleDbConnection connect = KetNoi.KetNoiDuLieu();
            string sql = "select TenKhachHang from KHACHHANG";
            OleDbCommand cmd = new OleDbCommand(sql, connect);
            AutoCompleteStringCollection list = new AutoCompleteStringCollection();
            OleDbDataReader r = cmd.ExecuteReader();
            while (r.Read())
            {
                string kq = (string)r[0];
                list.Add(kq);
            }
            r.Close();
            connect.Close();
            return list; 
        }
        public KhachHang LayKhachHangTheoTen(string tenkh)
        {
            OleDbConnection connect = KetNoi.KetNoiDuLieu();
            string sql = "select * from KHACHHANG where TenKhachHang=?";
            OleDbCommand cmd = new OleDbCommand(sql, connect);
            cmd.Parameters.Add("@TenKhachHang", OleDbType.VarWChar);
            cmd.Parameters[0].Value = tenkh;
            OleDbDataReader r = cmd.ExecuteReader();
            KhachHang khachhang = new KhachHang();
            while (r.Read())
            {
                khachhang.MaKH = (string)r[0];
                khachhang.TenKH = (string)r[1];
                khachhang.DiaChi = (string)r[2];
                khachhang.SoDT = (string)r[3];
                khachhang.TienNo = (decimal)r[4];
            }
            r.Close();
            connect.Close();
            return khachhang;
        }
        public KhachHang LayKhachHangTheoMa(string makh)
        {
            OleDbConnection connect = KetNoi.KetNoiDuLieu();
            string sql = "select * from KHACHHANG where MaKhachHang=?";
            OleDbCommand cmd = new OleDbCommand(sql, connect);
            cmd.Parameters.Add("@MaKhachHang", OleDbType.VarChar);
            cmd.Parameters[0].Value = makh;
            OleDbDataReader r = cmd.ExecuteReader();
            KhachHang khachhang = new KhachHang();
            while (r.Read())
            {
                khachhang.MaKH = (string)r[0];
                khachhang.TenKH = (string)r[1];
                khachhang.DiaChi = (string)r[2];
                khachhang.SoDT = (string)r[3];
                khachhang.TienNo = (decimal)r[4];
            }
            r.Close();
            connect.Close();
            return khachhang;
        }
        public bool KiemTraTenKhachHang(string tenkh)
        {
            OleDbConnection connect = KetNoi.KetNoiDuLieu();
            string sql = "select MaKhachHang from KHACHHANG where TenKhachHang=?";
            OleDbCommand cmd = new OleDbCommand(sql, connect);
            cmd.Parameters.Add("@TenKhachHang", OleDbType.VarWChar);
            cmd.Parameters[0].Value = tenkh;
            bool kq;
            OleDbDataReader r = cmd.ExecuteReader();
            kq = r.Read();
            r.Close();
            connect.Close();
            return kq;
        }
        public int DemSoKhachHang()
        {
            OleDbConnection connect = KetNoi.KetNoiDuLieu();
            string sql = "select Count(MaKhachHang) from KHACHHANG";
            OleDbCommand cmd = new OleDbCommand(sql, connect);
            int kq = 0;
            OleDbDataReader r = cmd.ExecuteReader();
            while (r.Read())
            {
                try
                {
                    kq = (int)r[0];
                }
                catch (Exception)
                { }
            }
            r.Close();
            connect.Close();
            return kq;
        }
    }
}
